<?php
/**
 * Product Reviews
 *
 * @package page
 * @copyright Copyright 2003-2006 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: header_php.php 3117 2006-03-05 20:38:44Z ajeh $
 */

  // This should be first line of the script:
  $zco_notifier->notify('NOTIFY_HEADER_START_PRODUCT_REVIEWS');

// check product exists and current
// if product does not exist or is status 0 send to _info page
    $products_reviews_check_query = "SELECT count(*) AS count
                                     FROM " . TABLE_PRODUCTS . " p
                                     WHERE p.products_id= :productsID
                                     AND p.products_status = 1";

    $products_reviews_check_query = $db->bindVars($products_reviews_check_query, ':productsID', $_GET['products_id'], 'integer');
    $products_reviews_check = $db->Execute($products_reviews_check_query);

    if ($products_reviews_check->fields['count'] < 1) {
      zen_redirect(zen_href_link(zen_get_info_page((int)$_GET['products_id']), 'products_id=' . (int)$_GET['products_id']));
    }

  $review_query_raw = "SELECT p.products_id, p.products_price, p.products_tax_class_id, p.products_image, p.products_model, pd.products_name
                       FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                       WHERE p.products_id = :productsID
                       AND p.products_status = 1
                       AND p.products_id = pd.products_id
                       AND pd.language_id = :languagesID";

  $review_query_raw = $db->bindVars($review_query_raw, ':productsID', $_GET['products_id'], 'integer');
  $review_query_raw = $db->bindVars($review_query_raw, ':languagesID', $_SESSION['languages_id'], 'integer');
  $review = $db->Execute($review_query_raw);

  $products_price = zen_get_products_display_price($review->fields['products_id']);

  if (zen_not_null($review->fields['products_model'])) {
    $products_name = $review->fields['products_name'];
  } else {
    $products_name = $review->fields['products_name'];
  }

// set image
//  $products_image = $review->fields['products_image'];
  if ($review->fields['products_image'] == '' and PRODUCTS_IMAGE_NO_IMAGE_STATUS == '1') {
    $products_image = PRODUCTS_IMAGE_NO_IMAGE;
  } else {
    $products_image = $review->fields['products_image'];
  }

 $review_status = " and r.status = 1";
 //Vu - edit line 57 -> 92 
  //$check = "SELECT review_parent_id FROM " . TABLE_REVIEWS_DESCRIPTION . " limit 1";
  //if (false === mysql_query($check)) {
  //     $db->Execute("ALTER TABLE " . TABLE_REVIEWS_DESCRIPTION . " ADD " . review_parent_id . " int(11) DEFAULT NULL ");      
  //}
  //var_dump("ALTER TABLE " . TABLE_REVIEWS_DESCRIPTION . " ADD " . review_parent_id . " int(11) DEFAULT NULL ");exit;
  $reviews_query_raw = "SELECT r.reviews_id, rd.reviews_text, r.reviews_rating, r.date_added, r.customers_name, rd.review_parent_id, r.customers_id, c.customers_nickname,
                        rd.reviews_title, IFNULL(r.reviews_votes,0) as reviews_votes, IFNULL(r.reviews_yes,0) as reviews_yes
                        FROM " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd, " . TABLE_CUSTOMERS . " c 
                        WHERE r.products_id = :productsID
                        AND c.customers_id = r.customers_id
                        AND r.reviews_id = rd.reviews_id " . $review_status . "
                        ORDER BY rd.languages_id ASC, (POW((r.reviews_yes / reviews_votes), 3 ) * LOG( reviews_votes )) DESC";
  
  $reviews_query_raw = $db->bindVars($reviews_query_raw, ':productsID', $_GET['products_id'], 'integer');
/*  $reviews_query_raw = $db->bindVars($reviews_query_raw, ':languagesID', $_SESSION['languages_id'], 'integer'); */
  $reviews_split = new splitPageResults($reviews_query_raw, 12);
  $reviews = $db->Execute($reviews_split->sql_query);
  $reviewsArray = array();
  while (!$reviews->EOF) {
  	$sql = "SELECT count(customers_id) as count FROM " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS_STATUS_HISTORY . " osh, " . TABLE_ORDERS . " o WHERE op.orders_id = o.orders_id AND osh.orders_id = o.orders_id AND osh.orders_status_id = 3 AND customers_id = :customersID AND products_id = :productsID";
        $sql .= " OR products_prid = :productsID";
        //$sql .= " OR products_id IN SELECT products_id FROM " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_ORDERS_STATUS_HISTORY . " osh, " . TABLE_ORDERS . " o WHERE (op.orders_id = o.orders_id AND osh.orders_id = o.orders_id AND osh.orders_status_id = 3 AND customers_id = :customersID AND products_id = :productsID)";
        $sql = $db->bindVars($sql, ':customersID', $reviews->fields['customers_id'], 'integer');
        $sql = $db->bindVars($sql, ':productsID', $_GET['products_id'], 'integer');
        $check = $db->Execute($sql);
        $owner = $check->fields['count'] > 0 ? '<p class = "owner_comment">"We certify this review since the customer bought this product with Suplementos Online"</p>':'';
  	$name = strlen($reviews->fields['customers_nickname'])>0?$reviews->fields['customers_nickname']:$reviews->fields['customers_name'] ;
        $name = '<a href="' . zen_href_link(FILENAME_PROFILE, 'nick=' . $name, 'SSL') . '">' . $name . '</a>';
        $reviewsArray[] = array('id'=>$reviews->fields['reviews_id'],
  	                        'customersName'=>$name,
  	                        'dateAdded'=>$reviews->fields['date_added'],
  	                        'reviewsText'=>$reviews->fields['reviews_text'],
  	                        'reviewsRating'=>$reviews->fields['reviews_rating'],
                                'reviewsTitle'=>$reviews->fields['reviews_title'],
                                'reviewsVotes'=>$reviews->fields['reviews_votes'],
                                'reviewsYes'=>$reviews->fields['reviews_yes'],
                                'reviewParentId'=>$reviews->fields['review_parent_id'],
                                'owner'=>$owner,
                                'full');
    $reviews->MoveNext();
  }
//Vu - end edit



  require(DIR_WS_MODULES . zen_get_module_directory('require_languages.php'));
  $breadcrumb->add(NAVBAR_TITLE);

  if(isset($_POST['reviews_yes_no']) && isset($_POST['reviews_yes_no_id']))
  {      
      if (!$_SESSION['customer_id']) {
          $_SESSION['navigation']->set_snapshot();
          zen_redirect(zen_href_link(FILENAME_LOGIN, '', 'SSL'));
      }
      $reviews_yes_no = (int)$_POST['reviews_yes_no'];
      $reviews_yes_no_id = $_POST['reviews_yes_no_id'];
      $reviews_votes_sql = "SELECT votes_id FROM votes WHERE reviews_id = :reviewsID AND customers_id = :customerID";
      $reviews_votes_sql = $db->bindVars($reviews_votes_sql, ':reviewsID', $reviews_yes_no_id, 'integer');
      $reviews_votes_sql = $db->bindVars($reviews_votes_sql, ':customerID', $_SESSION['customer_id'], 'integer');
      $reviews_votes_result = $db->Execute($reviews_votes_sql);
      if($reviews_votes_result->EOF)
      {          
          //insert into table votes
          $reviews_votes_sql_insert = "INSERT INTO votes (reviews_id, customers_id, yes_no_vote) VALUES(:reviewsID, :customerID, :yesnovote)";
          $reviews_votes_sql_insert = $db->bindVars($reviews_votes_sql_insert, ':reviewsID', $reviews_yes_no_id, 'integer');
          $reviews_votes_sql_insert = $db->bindVars($reviews_votes_sql_insert, ':customerID', $_SESSION['customer_id'], 'integer');
          $reviews_votes_sql_insert = $db->bindVars($reviews_votes_sql_insert, ':yesnovote', $reviews_yes_no, 'integer');
          $db->Execute($reviews_votes_sql_insert);
          
          //update reviews_yes and reviews_vote in table reviews
            if($reviews_yes_no === 1)
            {          
                $reviews_votes_sql_update = "UPDATE " . TABLE_REVIEWS . " SET reviews_yes = IFNULL(reviews_yes, 0) + 1, reviews_votes = IFNULL(reviews_votes, 0) + 1 WHERE reviews_id = :reviewsID";
                $reviews_votes_sql_update = $db->bindVars($reviews_votes_sql_update, ':reviewsID', $reviews_yes_no_id, 'integer');
                $db->Execute($reviews_votes_sql_update);
            }
            else if($reviews_yes_no === 0)
            {
                $reviews_votes_sql_update = "UPDATE " . TABLE_REVIEWS . " SET reviews_votes = IFNULL(reviews_votes, 0) + 1 WHERE reviews_id = :reviewsID";
                $reviews_votes_sql_update = $db->bindVars($reviews_votes_sql_update, ':reviewsID', $reviews_yes_no_id, 'integer');
                $db->Execute($reviews_votes_sql_update);
            }   
      }
         
  }
  
  // if review must be approved or disabled do not show review
    $review_status = " and r.status = '1'";

    $reviews_query = "select count(*) as count from " . TABLE_REVIEWS . " r, "
                                                       . TABLE_REVIEWS_DESCRIPTION . " rd
                       where r.products_id = '" . (int)$_GET['products_id'] . "'
                       and r.reviews_id = rd.reviews_id" .
                       $review_status;

    $reviews = $db->Execute($reviews_query);
    $reviews_average_rating_query = "select avg(reviews_rating) as average_rating from " . TABLE_REVIEWS . " r, "
                                                           . TABLE_REVIEWS_DESCRIPTION . " rd
                           where r.products_id = '" . (int)$_GET['products_id'] . "'
                           and r.reviews_id = rd.reviews_id" .
                           $review_status;

    $reviews_average_rating = $db->Execute($reviews_average_rating_query);
    $flag_show_product_info_reviews = zen_get_show_product_switch($_GET['products_id'], 'reviews');
    $flag_show_product_info_reviews_count = zen_get_show_product_switch($_GET['products_id'], 'reviews_count');
  // end of average review
  // This should be last line of the script:
  $zco_notifier->notify('NOTIFY_HEADER_END_PRODUCT_REVIEWS');
?>